package com.ctshk.rpc.order.tour.mapper;

import java.util.List;

import com.ctshk.rpc.order.tour.dto.*;
import com.ctshk.rpc.order.tour.dto.client.TouristCountDTO;
import com.ctshk.rpc.order.tour.dto.client.TouristInfoDTO;
import com.ctshk.rpc.order.tour.dto.custom.CustomOrderTouristInfoListCollectionDTO;
import com.ctshk.rpc.order.tour.req.custom.CustomOrderIdReq;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.ctshk.common.service.mapper.CtsBaseMapper;
import com.ctshk.rpc.order.tour.entity.SeriesOrderTouristInfo;
import com.ctshk.rpc.order.tour.req.SeriesOrderTouristInfoListCollectionReq;
import com.ctshk.rpc.order.tour.req.SeriesOrderTouristInfoListCollectionTripReq;
import com.ctshk.rpc.order.tour.req.SeriesOrderTouristInfoListReq;
import com.ctshk.rpc.order.tour.req.SeriesOrderTouristNameListReq;
import org.apache.ibatis.annotations.Update;

/**
 * <p>
 * 系列团游客信息 Mapper 接口
 * </p>
 *
 * @author 谭响
 * @since 2021-01-18
 */
public interface SeriesOrderTouristInfoMapper extends CtsBaseMapper<SeriesOrderTouristInfo> {

    @SelectProvider(type = SeriesOrderTouristInfoProvider.class, method = "selectListBySelective")
    IPage<SeriesOrderTouristInfoDTO> selectListBySelective(IPage<SeriesOrderTouristInfoListReq> page, @Param("req") SeriesOrderTouristInfoListReq req);

    @SelectProvider(type = SeriesOrderTouristInfoProvider.class, method = "selectCalculationBySelective")
    List<SeriesOrderCollectionCalculationDTO> selectCalculationBySelective(List<Long> touristIdList);

    @SelectProvider(type = SeriesOrderTouristInfoProvider.class, method = "selectListCollectionBySelective")
    IPage<CustomOrderTouristInfoListCollectionDTO> selectListCollectionBySelective(@Param("page") IPage<CustomOrderIdReq> pageParam, @Param("req") CustomOrderIdReq req);

    @SelectProvider(type = SeriesOrderTouristInfoProvider.class, method = "selectListCollectionTripBySelective")
    IPage<SeriesOrderTouristInfoListCollectionTripDTO> selectListCollectionTripBySelective(IPage<SeriesOrderTouristInfoListCollectionTripReq> page, @Param("req") SeriesOrderTouristInfoListCollectionTripReq req);


    @Select("<script>"+"SELECT t.id id, t.lock_status lockStatus,t.noShow noShow,t.en_fullname enFullname,t.cn_fullname cnFullname,\n" +
            "t.people_type peopleType ,t.tourist_type_name touristTypeName,t.pay_status payStatus,t.birthday birthday,t.gender gender,t.nation nation,t.mobile mobile,t.email email\n" +
            ",o.order_number orderNumber,o.order_source orderSource,t.create_id createId,\n" +
            "t.certificate_type1 certificateType,t.certificate_number1 certificateNumber,t.certificate_valid_date1\n" +
            "certificateValidDate,\n" +
            "t.certificate_type2 certificateType2,t.certificate_number2 certificateNumber2,t.certificate_valid_date2 certificateValidDate2,\n" +
            "t.certificate_type3 certificateType3,t.certificate_number3 certificateNumber3,t.certificate_valid_date3 certificateValidDate3, o.internal_remark internalRemark,\n" +
            "t.buy_visa buyVisa,v.collection_status collectionStatus,v.submit_time submitTime,v.submit_tracking_number submitTrackingNumber,v.make_up_time makeUpTime,v.make_up_tracking_number makeUpTrackingNumber, v.sign_out_status signOutStatus\n" +
            " FROM t_series_order_tourist_info t left join t_series_order o ON t.order_id = o.id left join t_series_order_tourist_visa v on t.id = v.Tourist_id  " +
            " LEFT JOIN `t_series_order_tourist_quit` t1 ON t.id=t1.tourist_id  where t.is_deleted = 0 and o.is_deleted = 0  AND (t1.is_deleted=0 OR  t1.is_deleted IS NULL) AND (t1.refund_status NOT IN(3) OR t1.refund_status IS NULL)  and t.schedule_id = #{req.scheduleId}\n" +
            "<if test=\"req.orderNumber != null and req.orderNumber != ''\">AND o.order_number LIKE CONCAT('%',#{req.orderNumber},'%')</if>\n" +
            "<if test=\"req.name != null and req.name != ''\">and (t.cn_fullname LIKE CONCAT('%',#{req.name},'%') or t.en_fullname LIKE CONCAT('%',#{req.name},'%'))</if>\n" +
            "<if test=\"req.touristTypeName != null and req.touristTypeName != ''\">AND t.tourist_type_name LIKE CONCAT('%',#{req.touristTypeName},'%')</if>\n" +
            "<if test=\"req.gender != null\">AND t.gender = #{req.gender}</if> \n" +
            "<if test=\"req.lockStatus != null\">AND t.lock_status = #{req.lockStatus}</if> \n" +
            "<if test=\"req.noShow != null\">AND t.noShow = #{req.noShow}</if> \n" +
            "<if test=\"req.payStatus != null\">AND t.pay_status = #{req.payStatus}</if> \n" +
            "<if test=\"req.orderSource != null\">AND o.order_source = #{req.orderSource}</if> \n" +
            "<if test=\"req.buyVisa != null\">AND t.buy_visa = #{req.buyVisa}</if> \n" +
            "<if test=\"req.collectionStatus != null\">AND v.collection_status = #{req.collectionStatus}</if> \n" +
            "<if test=\"req.signOutStatus != null\">AND v.sign_out_status = #{req.signOutStatus}</if> \n" +
            "ORDER BY t.gmt_create DESC"+"</script>")
    IPage<SeriesOrderTouristNameListDTO> queryTouristList(IPage<SeriesOrderTouristNameListDTO> page,@Param("req") SeriesOrderTouristNameListReq req);

    @Select("<script>"+"select t.main_data_id peopleType,count(1) peopleNum from t_series_order_tourist_info t WHERE t.schedule_id = #{sid} and t.is_deleted = 0  group by t.main_data_id"+"</script>")
    List<SeriesGroupTouristInfoDTO> groupEnrollNum(@Param("sid")Long id);

    @Select("<script>"+"select t.main_data_id peopleType, sum(sd.total_people) peopleNum from t_series_order_tourist_info t right join t_series_order sd on t.order_id = sd.id where t.is_deleted = 0 and sd.is_deleted = 0 and sd.plus_tourist_type = 2 and t.schedule_id = #{sid}  group by t.main_data_id"+"</script>")
    List<SeriesGroupTouristInfoDTO> groupSurplusNum(@Param("sid")Long id);

    /**
     * 统计团期下单人数
     * @param id
     * @return
     */
    @Select("SELECT COUNT(*) \n" +
            "FROM t_series_order_tourist_info\n" +
            "WHERE is_deleted = 0 AND schedule_id = #{schedule_id}")
    Long sumBySchedule(@Param("schedule_id") Long id);

    /**
     * C端统计订单下单人数
     * @param orderId
     * @return
     */
    @Select("SELECT COUNT(*) AS peopleNumber,tourist_type_name \n" +
            "FROM t_series_order_tourist_info\n" +
            "WHERE is_deleted = 0 AND order_id = #{order_id} GROUP BY main_data_id")
    List<TouristCountDTO> countByOrder(@Param("order_id") Long orderId);

    /**
     * C端订单旅客列表
     * @param orderId
     * @return
     */
    @Select({"SELECT main_data_id,tourist_type_name,email,cn_fullname,en_fullname,\n" +
            "gender,mobile_area_code,mobile,certificate_number1,certificate_type1,\n" +
            "certificate_type2,certificate_number2,certificate_number3,certificate_type3\n" +
            "FROM t_series_order_tourist_info\n" +
            "WHERE order_id = #{orderId}  AND is_deleted = #{isDelete}"})
    List<SeriesOrderTouristInfoDTO> queryListByOrder(@Param("orderId") Long orderId,@Param("isDelete") Integer isDelete);

    /**
     * 批量更新紧急联系人
     * @param orderId
     * @param emergencyContactName
     * @param emergencyMobileAreaCode
     * @param emergencyMobile
     */
    @Update("UPDATE t_series_order_tourist_info SET\n" +
            "emergency_contact_name = #{emergency_contact_name},emergency_mobile_area_code = #{emergency_mobile_area_code}, emergency_mobile = #{emergency_mobile}\n" +
            "WHERE order_id = #{order_id} AND is_deleted = 0")
    Integer applyEmergencyAll(@Param("order_id") Long orderId, @Param("emergency_contact_name") String emergencyContactName, @Param("emergency_mobile_area_code") String emergencyMobileAreaCode, @Param("emergency_mobile") String emergencyMobile);

    /**
     * 收款列表
     * @param pageParam
     * @param orderId
     * @return
     */
    @Select("select tsoti.id, tsoti.order_id, tsoti.main_data_id, tsoti.tourist_type_name,tsoti.collection_fee_json, \n" +
            "tsoti.cn_surname, tsoti.cn_name, tsoti.en_surname, tsoti.en_name, \n" +
            "tsoti.group_fee,tsoti.refund_amount as refundAmount, tsoti.pay_status, (SELECT SUM(t1.discount_amount) FROM t_series_order_discount t1 WHERE t1.check_status=3 AND t1.tourist_id=tsoti.id AND t1.order_id=73358561782005760) AS discountAmount,tsotq.refund_status AS quit_refund_status, \n" +
            "(SELECT group_concat( CONCAT( a.amount, '])', a.plus_type, '])', a.NAME ) SEPARATOR '})' ) FROM t_series_order_tourist_fee_adjust a WHERE a.tourist_id = tsoti.id AND a.order_id = #{order_id} ) feeAdjustDTOList, \n" +
//            "(SELECT group_concat( CONCAT( a.amount, '])', a.fee_name) SEPARATOR '})' ) FROM t_series_order_tourist_other_fee a WHERE a.tourist_id = tsoti.id AND a.order_id = #{order_id} AND a.fee_type = 1 ) otherFeeDTOList, \n" +
//            "(SELECT group_concat( CONCAT( a.amount, '])', a.fee_name) SEPARATOR '})' ) FROM t_series_order_tourist_other_fee a WHERE a.tourist_id = tsoti.id AND a.order_id = #{order_id} AND a.fee_type = 2 ) collectionFeeDTOList , \n" +
            "tsoti.refund_amount,tsoti.total_receivables, tsoti.total_paid_in,tsoti.remaining_receivables \n" +
            "from t_series_order_tourist_info tsoti \n" +
            "left join ( SELECT temp.tourist_id, temp.refund_status FROM t_series_order_tourist_quit temp ORDER BY temp.gmt_create DESC LIMIT 1 ) tsotq ON tsotq.tourist_id = tsoti.id \n" +
            "where tsoti.order_id = #{order_id} AND tsoti.is_full_info = 1 AND tsoti.is_deleted = 0")
    IPage<CustomOrderTouristInfoListCollectionDTO> queryTouristCollectionPage(@Param("pageParam") IPage<CustomOrderIdReq> pageParam, @Param("order_id") Long orderId);
    /**
     * 收客情况
     * @param orderId
     * @return
     */
    @Select("SELECT main_data_id, tourist_type_name, count(*) AS people FROM t_series_order_tourist_info " +
            "WHERE order_id = #{order_id} GROUP BY main_data_id")
    List<SeriesOrderPageTouristPeopleDTO> queryCount(@Param("order_id") Long orderId);

    /**
     * 批量关联分组id
     * @param idList
     * @param groupId
     * @param idList
     * @return
     */
    @Update("<script>"+"UPDATE t_series_order_tourist_info\n" +
            "SET group_id = #{groupId}\n" +
            "WHERE is_deleted = 0 AND id IN \n"+
            "<foreach collection=\"idList\" item=\"id\" index=\"index\" open=\"(\" close=\")\" separator=\",\">\n" +
            "#{id}\n" +
            "</foreach>"+
            "</script>")
    Integer batchUpdateGroupId(@Param("groupId")Long groupId, @Param("idList")List<Long> idList);
}
